import pymysql
import numpy as np
import pandas as pd
import pymysql.cursors
from sklearn.preprocessing import MinMaxScaler
from joblib import dump

class fyy5(object):
    def __init__(self):
        self.conn = pymysql.connect(
            host='127.0.0.1',
            user='root',
            password='1234568',
            database='sys',
            port=3306,
            charset='utf8'
        )
        # 注意：__init__ 方法不应该有返回语句
    
    def is_holiday(self, date):
        # 修复缩进，使其成为类的独立方法
        if date in ['2024-01-01', '2024-10-02', '2024-10-03', '2024-10-04', '2024-10-05', '2024-10-06', '2024-10-07', '2025-01-01', '2024-01-02', '2024-01-03']:
            return 1
        # 修复缩进，确保在 if 条件不满足时返回 0
        return 0

    def get_data(self):
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = """
        SELECT DATE(g.create_time) AS date, HOUR(g.create_time) AS hour, COUNT(*) AS count 
        FROM order_user_gate_rel g WHERE HOUR(g.create_time) BETWEEN 6 AND 23 GROUP BY date, hour
        """
        cursor.execute(sql)
        ret = cursor.fetchall()
        df = pd.DataFrame(ret)
        # print(df)
        # 转换格式
        date_range = pd.date_range(start='2024-07-01', end='2025-03-02', freq='D')
        hours = range(6, 24)
        full_index = pd.MultiIndex.from_product([date_range, hours], names=['date', 'hour'])
        df_full = df.set_index(['date', 'hour']).reindex(full_index, fill_value=0).reset_index()

        # 按天聚合数据，统计过去18个小时的结果次数
        df_pivot = df_full.pivot(index='date', columns='hour', values='count')
        print(df_pivot.head())

        # 对星期几和月份进行编码
        df_pivot['dow'] = df_pivot.index.dayofweek  # 星期几（0-6）
        df_pivot['month'] = df_pivot.index.month     # 月份
        df_pivot['is_holiday'] = df_pivot.index.map(self.is_holiday)     
        
        # 对星期几和月份进行独热编码
        df_pivot = pd.get_dummies(df_pivot, columns=['dow', 'month'], dtype=int)
        print(df_pivot.head())
        
        # 归一化小时列
        hours_columns = list(range(6, 24))
        df_hours = df_pivot[hours_columns].copy()
        feature_columns = [col for col in df_pivot.columns if col not in hours_columns]
        df_feature = df_pivot[feature_columns].copy()

        scaler = MinMaxScaler()
        scaler_hours = scaler.fit_transform(df_hours)
        #保存模型
        dump(scaler, 'fyy5/scaler.joblib')

        # 将归一化后的数据转换为DataFrame
        df_hours_scaled = pd.DataFrame(scaler_hours, columns=hours_columns, index=df_hours.index)

        # 合并
        df_pivot_clean = pd.concat([df_hours_scaled, df_feature], axis=1)
        df_pivot_clean.to_csv('fyy5/scenic_data.csv', index=False)  

if __name__ == '__main__':
    fyy = fyy5()
    fyy.get_data()